﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using DataModels;

/// <summary>
/// This class contains all of the functionality of the web service. Implemented in a static class for testing requirements
/// </summary>
namespace ServiceUtilities
{

    public static class ServiceUtilities
    {
        /// <summary>
        /// Creates a party in the database and returns the party code.
        /// Requires that the user has authenticated.
        /// 
        /// TODO: Party privacy and settings. Future Iteration
        /// </summary>
        /// <returns>
        /// returns the new party's partyid the application should use to identify it
        /// with theserver and the party access code that party host will give to his
        /// or her guests.
        /// </returns>
        public static CreatePartyData CreateParty(string connStr, string description, string accessCode, int accessLevel, int votesPerUser, int voteReplenish, bool allowsRequests)
        {
            // TODO: Authenticate session data
            //RequireAuthentication();

            Guid partyID = Guid.NewGuid();

            //TODO: take this in as a parameter or make random
            string partyAccessCode = accessCode;

            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Create party
                using (SqlCommand command = new SqlCommand("INSERT INTO Parties VALUES(@ID, @Created_Time," +
                                "@End_Time, @Description, @Party_Owner, @Access_Code, @Access_Level," +
                                " @Votes_Per_User, @Vote_Replenish, @Allows_Requests, @Now_Playing)", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("ID", partyID.ToString()));
                    command.Parameters.Add(new SqlParameter("Created_Time", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("End_Time", DateTime.Now.AddHours(24)));
                    command.Parameters.Add(new SqlParameter("Description", description));
                    command.Parameters.Add(new SqlParameter("Party_Owner", "fake_user_id")); //TODO: add real user ID
                    command.Parameters.Add(new SqlParameter("Access_Code", partyAccessCode));
                    command.Parameters.Add(new SqlParameter("Access_Level", accessLevel));
                    command.Parameters.Add(new SqlParameter("Votes_Per_User", votesPerUser));
                    command.Parameters.Add(new SqlParameter("Vote_Replenish", voteReplenish));
                    command.Parameters.Add(new SqlParameter("Allows_Requests", allowsRequests));
                    command.Parameters.Add(new SqlParameter("Now_Playing", String.Empty));
                    command.ExecuteNonQuery();
                }

                //Consider creating long queue row...

                //Create QueueData
                using (SqlCommand command = new SqlCommand("INSERT INTO QueueStatus VALUES(@Party_ID, @Last_Changed, @Songs_In_Queue)", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID.ToString()));
                    command.Parameters.Add(new SqlParameter("Last_Changed", DateTime.Now));
                    command.Parameters.AddWithValue("@Songs_In_Queue", 0);
                    command.ExecuteNonQuery();
                }

                //TODO: Create permissions

                myConnection.Close();

                // save the newly created party id in the session variable
                //HostPartyID = partyID.ToString();

                return new CreatePartyData() { PartyID = partyID.ToString(), AccessCode = partyAccessCode };
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return new CreatePartyData();
            }
        }

        /// <summary>
        /// Add a song to this party's playlist by adding it to Songs
        /// Requires a host session
        /// </summary>
        /// <param name="artist">Artist</param>
        /// <param name="album">Album</param>
        /// <param name="title">Song title</param>
        /// <param name="partyID">Party ID to add song to</param>
        /// <returns>ID of newly created song</returns>
        public static string AddSong(string connStr, string partyID, string artist, string album, string title)
        {
            //Generate song guid
            Guid songID = Guid.NewGuid();

            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Add song
                using (SqlCommand command = new SqlCommand("INSERT INTO Songs VALUES(@Party_ID, @Song_ID," +
                                "@Title, @Artist, @Album, @Up_Vote_Stat, @Down_Vote_Stat, @Last_Played)", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    command.Parameters.Add(new SqlParameter("Song_ID", songID.ToString()));
                    command.Parameters.Add(new SqlParameter("Title", title));
                    command.Parameters.Add(new SqlParameter("Artist", artist));
                    command.Parameters.Add(new SqlParameter("Album", album));
                    command.Parameters.AddWithValue("@Up_Vote_Stat", 0);
                    command.Parameters.Add(new SqlParameter("Down_Vote_Stat", -1));
                    //Need to pass parameter, and so we know it has not been played, make it in the future
                    command.Parameters.Add(new SqlParameter("Last_Played", DateTime.Now.AddYears(1)));
                    command.ExecuteNonQuery();
                }

                if (!isQueueFull(connStr, partyID))
                {
                    //Insert added song to queue
                    using (SqlCommand command = new SqlCommand("INSERT INTO Queues VALUES(@Party_ID, @Song_ID, @Up_Votes, @Down_Votes)", myConnection))
                    {
                        command.Parameters.Add(new SqlParameter("Song_ID", songID));
                        command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                        command.Parameters.AddWithValue("@Up_Votes", 0);
                        command.Parameters.Add(new SqlParameter("Down_Votes", -1));
                        command.ExecuteNonQuery();
                    }
                }
                //Return new ID if success
                return songID.ToString();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                //Return empty string upon failure
                return String.Empty;
            }
        }

        /// <summary>
        /// Remove a song from the song library and queues
        /// </summary>
        /// <param name="partyID">Current party ID</param>
        /// <param name="songID">Song to remove</param>
        /// <returns>Returns true if successful</returns>
        public static bool RemoveSong(string connStr, string partyID, string songID)
        {
            bool songInQueue = false;
            SqlDataReader reader;
            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Delete song
                using (SqlCommand command = new SqlCommand("DELETE FROM Songs WHERE Song_ID=@Song_ID AND Party_ID=@Party_ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Song_ID", songID));
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    command.ExecuteNonQuery();
                }

                //Check to see if song is in the queue
                using (SqlCommand command = new SqlCommand("SELECT * FROM Queues WHERE Party_ID=@Party_ID AND Song_ID=@Song_ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    command.Parameters.Add(new SqlParameter("Song_ID", songID));
                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        songInQueue = true;
                    }
                    reader.Close();
                }

                if (songInQueue)
                {
                    //Delete song from queues
                    using (SqlCommand command = new SqlCommand("DELETE FROM Queues WHERE Song_ID=@Song_ID AND Party_ID=@Party_ID", myConnection))
                    {
                        command.Parameters.Add(new SqlParameter("Song_ID", songID));
                        command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                        command.ExecuteNonQuery();
                    }

                    //Decrease songs in queue
                    using (SqlCommand command = new SqlCommand("UPDATE QueueStatus SET Songs_In_Queue=Songs_In_Queue - 1 WHERE Party_ID=@Party_ID", myConnection))
                    {
                        command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                        command.ExecuteNonQuery();
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return false;
            }
        }

        /// <summary>
        /// Called by the desktop to update the now-playing song
        /// 
        /// TODO: Will need much better queuing algorithm. In future iteration
        ///     we will decide on this
        /// </summary>
        /// <param name="songID">Song ID to set now playing</param>
        public static bool UpdateNowPlaying(string connStr, string partyID, string songID)
        {
            //Set now playing song to new song
            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Create party
                using (SqlCommand command = new SqlCommand("UPDATE Parties SET Now_Playing=@Now_Playing WHERE ID=@ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Now_Playing", songID));
                    command.Parameters.Add(new SqlParameter("ID", partyID));                     	
                    command.ExecuteNonQuery(); 	
                }

                // See if the song was in the queue
 	
                bool inQueue = false;

                using (SqlCommand command = new SqlCommand("SELECT * FROM Queues WHERE Song_Id=@Song_ID AND Party_ID=@Party_ID", myConnection))
                {

                    command.Parameters.Add(new SqlParameter("Song_ID", songID));
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    SqlDataReader reader = command.ExecuteReader();
                    if (reader.HasRows)
                        inQueue = true;
                }	 
 	
                // If not in queue, do nothing 	
                if (!inQueue) 	
                    return true;
	
                // If it was in queue, remove the song from queue first	
                using (SqlCommand command = new SqlCommand("DELETE FROM Queues WHERE Song_ID=@Song_ID AND Party_ID=@Party_ID", myConnection))	
                {	
                    command.Parameters.Add(new SqlParameter("Song_ID", songID));
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));	
                    command.ExecuteNonQuery();	
                }

                // Now a new song needs to be queued. Select random song from playlist
                string songIdToAdd = null;	
                using (SqlCommand command = new SqlCommand("SELECT * FROM Songs WHERE Party_ID=@Party_ID ORDER BY NEWID()", myConnection))	
                { 	
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    SqlDataReader reader = command.ExecuteReader();
                    if (!reader.Read())	
                        throw new Exception();	
                    songIdToAdd = (string)reader["Song_ID"];	
                } 	
 	
                // then add it to the queue	
                using (SqlCommand command = new SqlCommand("INSERT INTO Queues VALUES(@Party_ID, @Song_ID, @Up_Votes, @Down_Votes", myConnection))	
                { 	
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));	
                    command.Parameters.Add(new SqlParameter("Song_ID", songIdToAdd));
                    command.Parameters.AddWithValue("@Up_Votes", 0); 	
                    command.Parameters.Add(new SqlParameter("Down_Votes", -1));	
  
                    command.ExecuteNonQuery();
                }

                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return false;
            }
        }

        //---------------------------------------------------------------------------------//


        /*---------------------------------------------------------------------------------*\
     * Methods used by the desktop application and also the mobile application
     * No authentication is necessary.
    \*---------------------------------------------------------------------------------*/

        /// <summary>
        /// Call to upvote a song
        /// </summary>
        /// <param name="partyID">Current party ID</param>
        /// <param name="songID">Song to upvote</param>
        /// <returns>Does queue need to be updated</returns>
        public static bool UpvoteSong(string connStr, string partyID, string songID)
        {
            //Ideally this should also be done using stored procedures; make changes if time

            SqlDataReader reader;
            int voteCount = -1;
            string lowestCountSongID = "";
            int lowestQueueVoteCount = -1;
            bool songInQueue = false;
            //this will be implemented fully in the next iteration

            //Update song vote count
            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Update vote count
                using (SqlCommand command = new SqlCommand("UPDATE Songs SET Up_Vote_Stat=Up_Vote_Stat + 1 WHERE Party_ID=@Party_ID AND Song_ID=@Song_ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    command.Parameters.Add(new SqlParameter("Song_ID", songID));
                    command.ExecuteNonQuery();
                }

                //Check to see if song is in Queue
                using (SqlCommand command = new SqlCommand("SELECT * FROM Queues WHERE Party_ID=@Party_ID AND Song_ID=@Song_ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    command.Parameters.Add(new SqlParameter("Song_ID", songID));
                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        songInQueue = true;
                    }
                    reader.Close();
                }

                //Update its count in queue if so, otherwise do stuff below
                if (songInQueue)
                {
                    using (SqlCommand command = new SqlCommand("UPDATE Queues SET Up_Votes=Up_Votes + 1 WHERE Party_ID=@Party_ID AND Song_ID=@Song_ID", myConnection))
                    {
                        command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                        command.Parameters.Add(new SqlParameter("Song_ID", songID));
                        command.ExecuteNonQuery();
                    }
                }
                else
                {
                    //Get vote count for song
                    using (SqlCommand command = new SqlCommand("SELECT * FROM Songs WHERE Party_ID=@Party_ID AND Song_ID=@Song_ID", myConnection))
                    {
                        command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                        command.Parameters.Add(new SqlParameter("Song_ID", songID));
                        reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            Int32.TryParse(reader["Up_Vote_Stat"].ToString(), out voteCount);
                        }
                        reader.Close();
                    }

                    //Get song with lowest vote count in Queue
                    using (SqlCommand command = new SqlCommand("select yt.Song_ID, yt.Up_Votes from Queues yt inner join(select Song_ID, min(Up_Votes) Up_Votes from Queues group by Song_ID) ss on yt.Song_ID = ss.Song_ID and yt.Up_Votes = ss.Up_Votes", myConnection))
                    {
                        command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                        reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            Int32.TryParse(reader["Up_Votes"].ToString(), out lowestQueueVoteCount);
                            lowestCountSongID = reader["Song_ID"].ToString();
                        }
                        reader.Close();
                    }

                    //If song with lowest count in queue has less votes than upvoted song, remove one, add the other
                    //then update last time queue was changed
                    //Queue should always have 20 songs in it, so might want to check to make
                    //sure the queue is full, before removing songs
                    if (lowestQueueVoteCount < voteCount)
                    {
                        //Delete old song from queues
                        using (SqlCommand command = new SqlCommand("DELETE FROM Queues WHERE Song_ID=@Song_ID AND Party_ID=@Party_ID", myConnection))
                        {
                            command.Parameters.Add(new SqlParameter("Song_ID", lowestCountSongID));
                            command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                            command.ExecuteNonQuery();
                        }

                        //Insert upvoted song into queue
                        using (SqlCommand command = new SqlCommand("INSERT INTO Queues VALUES(@Party_ID, @Song_ID, @Up_Votes, @Down_Votes)", myConnection))
                        {
                            command.Parameters.Add(new SqlParameter("Song_ID", songID));
                            command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                            //In case it is 0
                            command.Parameters.AddWithValue("@Up_Votes", voteCount);
                            command.Parameters.Add(new SqlParameter("Down_Votes", -1));
                            command.ExecuteNonQuery();
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return false;
            }
        }

        /// <summary>
        /// Call to downvote song
        /// </summary>
        /// <param name="partyID">Current party ID</param>
        /// <param name="songID">Song ID to downvote</param>
        /// <returns>Does queue need to be updated</returns>

        public static bool DownvoteSong(string connStr, string partyID, string songID)
        {
            //this will be implemented fully in the next iteration

            //Update song vote count

            //Get queue, check to see if song is in queue

            //If song is in queue, run helper to see if queue needs to be changed
            //look into using stored procs for this

            throw new NotImplementedException();
        }

        /// <summary>
        /// Returns the current data of the song that is playing
        /// </summary>
        /// <param name="partyID">ID of the party</param>
        /// <returns>Song data of the song currently playing</returns>
        public static SongData GetNowPlaying(string connStr, string partyID)
        {
            SqlDataReader reader;

            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Get ID
                using (SqlCommand command = new SqlCommand("SELECT * FROM Parties, Songs Where Parties.ID=@ID AND Songs.Song_ID=Parties.Now_Playing", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("ID", partyID));
                    reader = command.ExecuteReader();

                    reader.Read();
                    return new SongData()
                    {
                        SongID = reader["Now_Playing"].ToString(),
                        Artist = reader["Artist"].ToString(),
                        Album = reader["Album"].ToString(),
                        Title = reader["Title"].ToString()
                    };
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return new SongData();
            }
        }

        /// <summary>
        /// Gets queue data for a given party
        /// </summary>
        /// <param name="partyID">ID of party</param>
        /// <returns>List of queue data</returns>
        public static List<QueueData> GetQueue(string connStr, string partyID)
        {
            List<QueueData> PartyQueue = new List<QueueData>();
            SqlDataReader reader;

            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Create party
                using (SqlCommand command = new SqlCommand("SELECT * FROM Queues, Songs WHERE Queues.Party_ID=@Party_ID AND Queues.Song_ID=Songs.Song_ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    reader = command.ExecuteReader();
                }

                while (reader.Read())
                {
                    PartyQueue.Add(new QueueData()
                    {
                        Song = new SongData()
                        {
                            Title = reader["Title"].ToString(),
                            Artist = reader["Artist"].ToString(),
                            Album = reader["Album"].ToString(),
                            SongID = reader["Song_ID"].ToString()
                        },
                        UpVotes = Int32.Parse(reader["Up_Votes"].ToString()),
                        DownVotes = Int32.Parse(reader["Down_Votes"].ToString())
                    });
                }

                //load the queue items from the database into the PartyQueue list
                //return the list in sorted order
                //TODO: sort, pending discussion
                return PartyQueue;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return null;
            }
        }

        private static bool isQueueFull(string connStr, string partyID)
        {
            SqlDataReader reader;
            int count = 0;

            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Create party
                using (SqlCommand command = new SqlCommand("SELECT Songs_In_Queue FROM QueueStatus WHERE Party_ID=@Party_ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    reader = command.ExecuteReader();
                }

                while (reader.Read())
                {
                    Int32.TryParse(reader["Songs_In_Queue"].ToString(), out count);
                }

                //load the queue items from the database into the PartyQueue list
                //return the list in sorted order
                //TODO: sort, pending discussion
                return count >= 20;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return false;
            }
        }

        /// <summary>
        /// Returns the last tame a queue for a party was changed
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="partyID"></param>
        /// <returns></returns>
        public static DateTime GetLastQueueChange(string connStr, string partyID)
        {
            SqlDataReader reader;
            DateTime lastChanged = DateTime.Now.AddYears(-1);

            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Create party
                using (SqlCommand command = new SqlCommand("SELECT Last_Changed FROM QueueData WHERE Party_ID=@Party_ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    reader = command.ExecuteReader();
                }

                while (reader.Read())
                {
                    lastChanged = (DateTime)reader["Songs_In_Queue"];
                }

                //load the queue items from the database into the PartyQueue list
                //return the list in sorted order
                //TODO: sort, pending discussion
                return lastChanged;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return DateTime.MinValue;
            }
        }

        /// <summary>
        /// Returns the list of all songs in the party
        /// </summary>
        /// <param name="partyID"></param>
        /// <returns></returns>
        public static List<SongData> GetAllSongs(string connStr, string partyID)
        {
            List<SongData> Songs = new List<SongData>();
            SqlDataReader reader;

            try
            {
                SqlConnection myConnection = new SqlConnection(connStr);
                myConnection.Open();

                //Get songs
                using (SqlCommand command = new SqlCommand("SELECT * FROM Songs WHERE Party_ID=@Party_ID", myConnection))
                {
                    command.Parameters.Add(new SqlParameter("Party_ID", partyID));
                    reader = command.ExecuteReader();
                }

                while (reader.Read())
                {
                    Songs.Add(new SongData()
                    {
                        Title = reader["Title"].ToString(),
                        Artist = reader["Artist"].ToString(),
                        Album = reader["Album"].ToString(),
                        SongID = reader["Song_ID"].ToString()
                    });
                }

                //load the queue items from the database into the PartyQueue list
                //return the list in sorted order
                //TODO: sort, pending discussion
                return Songs;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return null;
            }
        }
    }
}
